package org.team4.constants;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库常量
 * 
 * @author MarkerKing
 *
 */
public final class SQLConstant {
	private SQLConstant(){}
	
	public static final List<String> DEPARTMENT_DELETE_LIST = new ArrayList<String>();
	public static final List<String> ROLE_DELETE_LIST = new ArrayList<String>();
	
	//字典修改或删除时同步更新的关联语句
	public static final Map<String, List<String>> DICT_UPDATE_MAP = new HashMap<String, List<String>>();

	public static final String USERS_ALL_HQL = "from Users order by department.name,realName";
	public static final String USERS_NOSELF_ALL_HQL = "from Users where id <> ? order by realName";
	public static final String USERS_DEPT_H_HQL = "from Users where department.id = ?";
	public static final String USERS_ALL_H_HQL = "from Users where 1=1";
	public static final String USERS_LIKE_REALNAME_HQML = "from Users where realName like ?";

	public static final String DEPARTMENT_PAGE_HQL = "from Department where 1=1";
	public static final String DEPARTMENT_CHILD_HQL = "from Department where superId = ?";
	public static final String DEPARTMENT_TREE_HQL = "from Department";
	public static final String DEPARTMENT_CHILD_LIKE = "from Department where code like ?";
	public static final String DEPARTMENT_NOT_LIKE = "from Department where code not like ? order by code";
	public static final String DEPARTMENT_STORE_LIKE = "from Department where code like ? and isStore = 'T' order by code";
	public static final String DEPARTMENT_STORE = "from Department where isStore = 'T' ";

	// 字典分页
	public static final String DICTIONARY_PAGE_HQL = "from Dictionary where superId = ?";
	// 查找下级字典
	public static final String DICTIONARY_CHILD_HQL = "from Dictionary where superId = ? order by sortNum";
	// 查找某字典类下的最大字典排序号
	public static final String DICTIONARY_NEXT_SORTNUM = "select max(sortNum) as nextNum from Dictionary where superId = ?";
	// 查找同类是否有重名字典
	public static final String DICTIONARY_VALIDATE_NAME = "from Dictionary where name = ? and superId = ?";
	public static final String DICTIONARY_UPDATE_VALIDATE_NAME = "from Dictionary where id <> ? and name = ? and superId = ?";

	public static final String INTERCOM_SEND_ALL_HQL = "from Intercom i where i.sender.id = ? and i.owner.id = ? order by sendTime desc";
	public static final String INTERCOM_READFLAG_HQL = "from Intercom i where i.owner = ? and i.readFlag = ? order by sendTime desc";
	public static final String INTERCOM_INFORM_HQL = "from Intercom i where i.sender.id = ? and i.inform = ? and i.readFlag = 'T' order by sendTime desc";
	public static final String INTERCOM_PAGE_HQL = "from Intercom where readFlag = ? and owner = ?";
	public static final String INTERCOM_OWNER_PAGE_HQL = "from Intercom where owner = ?";

	// 系统日志分页
	public static final String SYSLOG_PAGE_HQL = "from Syslog";
	public static final String SYSLOG_BY_TYPE_PAGE_HQL = "from Syslog where type = ?";

	// 系统消息分页
	public static final String SYSMESUSER_PAGE_HQL = "from SysMesUser where users = ?";
	public static final String SYSMESUSER_BY_READFLAG_PAGE_HQL = "from SysMesUser where readFlag = ? and users = ?";
	// 未读系统消息
	public static final String SYSMESUSER_BY_READFLAG_HQL = "from SysMesUser where readFlag = ? and users = ?";
	public static final String SYSMESUSER_ALL_HQL = "from SysMesUser where users = ?";

	public static final String ROLE_PAGE_HQL = "from Role where 1=1";

	// 根据对象ID查找附件
	public static final String ATTACHEDFILE_BY_SUPERID_HQL = "from AttachedFile where superId = ?";

	public static final String MODULE_BY_SUPERID_HQL = "from VmetaModule where superId = ?";
	public static final String MODULE_ALL_HQL = "from VmetaModule order by superId";
	public static final String MODULE_CHILD_HQL = "from VmetaModule where superId <> ?";

	public static final String OPERATE_BY_MODULE_HQL = "from VmetaOperate where module.id = ?";
	public static final String OPERATE_PAGE_ALL_HQL = "from VmetaOperate";
	public static final String OPERATE_BY_MODULE_VISIBLE_HQL = "from VmetaOperate where module.id = ? and scopeDataVisible = 'T'";

	public static final String FIELD_BY_MODULE_HQL = "from VmetaField where module.id = ?";
	public static final String FIELD_PAGE_ALL_HQL = "from VmetaField";

	public static final String ROLEOPERATE_BY_MODULE_ROLE_HQL = "from RoleModuleOperate where module.id = ? and role.id = ?";
	public static final String ROLEOPERATE_BY_WEBID_ROLEID_HQL = "from RoleModuleOperate where webId = ? and role.id = ?";
	public static final String ROLEOPERATE_BY_ROLEID_HQL = "from RoleModuleOperate where role.id = ?";

	public static final String ROLEMODULEUSERS_BY_ROLE_MODULE_HQL = "from RoleModuleUsers where role.id = ? and module.id = ?";

	public static final String ROLEMODULEFIELD_BY_MODULE_ROLE_HQL = "from RoleModuleField where module.id = ? and role.id = ?";
	public static final String ROLEMODULEFIELD_BY_ROLE_WEBID_HQL = "from RoleModuleField where role.id = ? and webId = ?";
	public static final String ROLEMODULEFIELD_BY_ROLE_MODULE_WEBID_HQL = "from RoleModuleField where role.id = ? and module.id = ? and webId = ?";
	public static final String ROLEMODULEFIELD_BY_ROLEID_HQL = "from RoleModuleField where role.id = ?";

	public static final String ROLEUSERS_BY_USERS_HQL = "from RoleUsers where users.id = ?";
	public static final String ROLEUSERS_BY_ROLE_HQL = "from RoleUsers where role.id = ?";
	public static final String ROLEUSERS_BY_USERS_ROLE_HQL = "from RoleUsers where users.id = ? and role.id = ?";

	public static final String ROLEMODULEDEPARTMENT_BY_ROLE_MODULE_HQL = "from RoleModuleDepartments where role.id = ? and module.id = ?";
	public static final String ROLEMODULEDEPARTMENT_LIKE_DEPARTMENTS = "from RoleModuleDepartments where departments like ?";

	public static final String SCOPEDATAVISIBLE_BY_ROLE_MODULE_USERS_HQL = "from ScopeDataVisible where role.id = ? and module.id = ? and users.id = ?";
	public static final String SCOPEDATAVISIBLE_BY_ROLE_MODULE_USERS_WEBID_HQL = "from ScopeDataVisible where role.id = ? and module.id = ? and users.id = ? and webId = ?";
	public static final String SCOPEDATAVISIBLE_BY_ROLE_HQL = "from ScopeDataVisible where role.id = ?";
	
	// 查找同类是否有重名角色
	public static final String ROLE_VALIDATE_NAME = "from Role where name = ?";
	public static final String ROLE_UPDATE_VALIDATE_NAME = "from Role where id <> ? and name = ? ";

	public static final Map<String, List<String>> DELETE_TABLE_VALIDATE = new HashMap<String, List<String>>();

	static {
		DEPARTMENT_DELETE_LIST.add("from Department where superId = ?");
		DEPARTMENT_DELETE_LIST.add("from Users where department.id = ?");

		ROLE_DELETE_LIST.add("from RoleUsers where role.id = ?");

		DELETE_TABLE_VALIDATE.put("Department", DEPARTMENT_DELETE_LIST);
		DELETE_TABLE_VALIDATE.put("Role", ROLE_DELETE_LIST);
	}

	/**
	 * 获得拼音查询的语句
	 * 
	 * @param table
	 * @param property
	 * @param azparam
	 * @return
	 */
	public static String getAZHQL(String table, String property, String azparam) {
		StringBuilder sb = new StringBuilder();
		sb.append("from ");
		sb.append(table);
		if ("ALL".equals(azparam)) {
			sb.append(" where 1=1");
		} else if ("0~9".equals(azparam)) {
			sb.append(" where (ISNUMERIC(dbo.getPinYin(SUBSTRING(");
			sb.append(property);
			sb.append(", 1, 1))) = 1)");
		} else {
			sb.append(" where dbo.getPinYin(SUBSTRING(");
			sb.append(property);
			sb.append(", 1, 1)) = '");
			sb.append(azparam);
			sb.append("' ");
		}
		return sb.toString();
	}

	/**
	 * 获得数据范围的子句
	 * 
	 * @param usersIds
	 * @return
	 */
	public static String getUsersWhere(String usersIds) {
		return getSQLInString(usersIds, "creator.id", "and");
	}
	
	/**
	 * 获得数据范围的子句
	 * 
	 * @param usersIds
	 * @return
	 */
	public static String getSQLUsersWhere(String usersIds) {
		return getSQLInString(usersIds, "creator", "and");
	}
	
	public static String getSQLInString(String str, String property, String orAnd) {
		StringBuilder sb = new StringBuilder();
		sb.append(" ");
		sb.append(orAnd);
		sb.append(" ");
		sb.append(property);
		sb.append(" in (");
		sb.append(str);
		sb.append(")");
		return sb.toString();
	}
}